/*
 * ReportsBroker.java
 *
 * Created on 13 de agosto de 2008, 04:59 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package flexbrokers;

import helpers.NumberHelper;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import listeners.InitApp;
import model.Cheque;
import model.Cupo;
import model.Customer;
import model.Order;
import model.Salesman;
import flex.messaging.io.ArrayCollection;
import flex.messaging.io.amf.ASObject;
import org.apache.commons.beanutils.BeanUtils;
import reports.ReporteBase;

/**
 *
 * @author Alirio
 */
public class ReportsBroker {
    
    /** Creates a new instance of ReportsBroker */
    public ReportsBroker() {
    }
    
    public void saveOrUpdateCupo(Object  object) {
        
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        String sql="";
        
        /* ASObject es el tipo de objeto que Flex nos envia, en realidad es un Map */
        ASObject asobject = (ASObject) object;
        Cupo cupo =new Cupo();
        try {
            /* BeanUtils agarra el objeto salesman y le llena sus valores con el Map Asobject */
            BeanUtils.populate(cupo, asobject);
        } catch (IllegalAccessException ex) {
            ex.printStackTrace();
        } catch (InvocationTargetException ex) {
            ex.printStackTrace();
        }
        
         
        try {
            
        stmt = cnn.createStatement();
        java.sql.ResultSet rs1 = stmt.executeQuery("select * from products where product_id='"+ cupo.getProducto() +"'");
       while (rs1.next())
       {
         //cupo.setMarca(rs1.getString("brand"));
       }
            
            
            sql="select * from cupo where vendedor='"+ cupo.getVendedor()  +"' and marca='"+ cupo.getMarca() +"' and producto='"+ cupo.getProducto() +"'  ";
            java.sql.ResultSet rs = stmt.executeQuery(sql);
            
            
            if(rs.next()) {
                stmt.executeUpdate("update  cupo set desde='"+ cupo.getDesdeDate() +"',hasta='"+ cupo.getHastaDate() +"',cantidad="+ NumberHelper.formatNumerForSQL(cupo.getCantidad()) +",consumido="+ NumberHelper.formatNumerForSQL(cupo.getConsumido()) +",restante="+ NumberHelper.formatNumerForSQL(cupo.getCantidad()-cupo.getConsumido())  +",description='"+ cupo.getDescription() +"'  where vendedor='"+ cupo.getVendedor()  +"' and marca='"+ cupo.getMarca() +"' and producto='"+ cupo.getProducto() +"' ");
            } else {
                stmt.executeUpdate(" insert into cupo values ('"+ cupo.getVendedor()  +"','"+ cupo.getMarca()  +"','"+ cupo.getProducto()  +"','"+ cupo.getDesdeDate() +"','"+ cupo.getHastaDate() +"',"+ NumberHelper.formatNumerForSQL(cupo.getCantidad()) +","+ NumberHelper.formatNumerForSQL(cupo.getConsumido()) +","+ NumberHelper.formatNumerForSQL(cupo.getCantidad()-cupo.getConsumido())  +",'"+ cupo.getDescription() +"')");
            }
            
            stmt.close();
            
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        
        
        
    }
    
    public void eliminarCupo(String producto,String vendedor) {
        
        
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        List list = new ArrayList();
        String sql="delete from cupo where vendedor='"+ vendedor +"' and producto='"+ producto +"'";
        
        try {
            stmt = cnn.createStatement();
            stmt.executeUpdate(sql);
            
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    
    
     public void eliminarCupo(String sql) {
        
        
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        List list = new ArrayList();
        //String sql="delete from cupo where vendedor='"+ vendedor +"' and producto='"+ producto +"'";
        
        try {
            stmt = cnn.createStatement();
            stmt.executeUpdate(sql);
            
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    
    
    
    public List productosPorVendedor(String brand, String vendedor,String tipo) {
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        List list = new ArrayList();
        String sql="";
        
        if (tipo.compareTo("filtrado")==0) {
            sql="SELECT  products.product_id as producto, products.description, Cupo.desde, Cupo.hasta, Cupo.cantidad, Cupo.consumido,Cupo.restante FROM   products LEFT OUTER JOIN  Cupo ON products.product_id = Cupo.producto where cupo.vendedor like '%"+ vendedor +"%'";
        } else {
            
            sql= "SELECT  cupo.producto, description, Cupo.desde, Cupo.hasta, Cupo.cantidad, Cupo.consumido,Cupo.restante FROM  Cupo where marca='"+ brand +"' and vendedor='"+ vendedor +"'" +
                    " union" +
                    " SELECT  products.product_id, products.description, '' as desde, '' as hasta, 0 as cantidad, 0 as consumido,0 as restante FROM   products where brand='"+ brand +"' and product_id not in (select producto from cupo where marca='"+ brand +"' and vendedor='"+ vendedor +"')";
        }
        
        
        try {
            stmt = cnn.createStatement();
            java.sql.ResultSet rs = stmt.executeQuery(sql);
            
            
            while (rs.next()) {
                Cupo cupo = new Cupo();
                cupo.setProducto(rs.getString("producto"));
                cupo.setDescription(rs.getString("description"));
                cupo.setDesdeDate(rs.getString("desde"));
                cupo.setHastaDate(rs.getString("hasta"));
                cupo.setCantidad(rs.getDouble("cantidad"));
                cupo.setConsumido(rs.getDouble("consumido"));
                cupo.setRestante(rs.getDouble("restante"));
                list.add(cupo);
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return list;
    }
    
    
     public List resumenCupos() {
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        List list = new ArrayList();
        String sql="";
        
      sql="SELECT  products.product_id as producto, products.description, Cupo.desde, Cupo.hasta, isnull(sum(Cupo.cantidad),0) as cantidad, isnull(sum(Cupo.consumido),0) as consumido,isnull(sum(Cupo.restante),0) as restante FROM   products LEFT OUTER JOIN  Cupo ON products.product_id = Cupo.producto  group by product_id,products.description,desde,hasta";
        
        
        try {
            stmt = cnn.createStatement();
            java.sql.ResultSet rs = stmt.executeQuery(sql);
            
            
            while (rs.next()) {
                Cupo cupo = new Cupo();
                cupo.setProducto(rs.getString("producto"));
                cupo.setDescription(rs.getString("description"));
                cupo.setDesdeDate(rs.getString("desde"));
                cupo.setHastaDate(rs.getString("hasta"));
                cupo.setCantidad(rs.getDouble("cantidad"));
                cupo.setConsumido(rs.getDouble("consumido"));
                cupo.setRestante(rs.getDouble("restante"));
                list.add(cupo);
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return list;
    }
    
    
    
    
    public List listOrdersByBrand(String brand, String desde, String hasta) {
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        List list = new ArrayList();
        String filter;
        if (brand.equals("[Todas]")) {
            filter = "";
        } else {
            filter = " and products.brand='" + brand + "' ";
        }
        try {
            stmt = cnn.createStatement();
            java.sql.ResultSet rs = stmt.executeQuery(
                    "select orders.order_id, orders.order_date, orders.required_date, orders.status " +
                    "from orders, orders_details, products " +
                    "where orders.order_date>='"+desde+"' and " +
                    "orders.order_date<='"+hasta+"' and " +
                    "orders.order_id=orders_details.order_id and " +
                    "orders_details.product_id=products.product_id  " +
                    filter +
                    "group by orders.order_id, orders.order_date, orders.required_date, orders.status " +
                    "order by orders.order_date");
            while (rs.next()) {
                Order order = new Order();
                order.setId(rs.getLong("order_id"));
                order.setDate(rs.getString("order_date"));
                order.setRequiredDate(rs.getString("required_date"));
                order.setStatus(rs.getString("status"));
                list.add(order);
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return list;
    }
    
    public List listOrdersBySalesman(String salesman, String desde, String hasta, String boss, String status) {
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        List list = new ArrayList();
        String sql;
        String statusFilter;
        if (status.equals("[Todos]")) {
            statusFilter = "";
        } else {
            statusFilter = "orders.status='" + status + "' and ";
        }
        if (salesman.equals("[Todos]")) {
            sql="select orders.order_id, orders.order_date, orders.required_date, orders.status,orders.salesman_id " +
                    "from orders " +
                    "where orders.order_date>='" + desde + "' and " +
                    "orders.order_date<='" + hasta + "' and " +
                    statusFilter +
                    "orders.salesman_id IN (select salesmen_sub.salesman_id_sub from salesmen_sub where salesmen_sub.salesman_id='"+boss+"') " +
                    "order by orders.order_date";
        } else {
            sql="select orders.order_id, orders.order_date, orders.required_date, orders.status,orders.salesman_id " +
                    "from orders " +
                    "where orders.order_date>='"+desde+"' and " +
                    "orders.order_date<='"+hasta+"' and " +
                    statusFilter +
                    "orders.salesman_id='" + salesman + "' order by orders.order_date";
        }
        try {
            stmt = cnn.createStatement();
            java.sql.ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Order order = new Order();
                order.setId(rs.getLong("order_id"));
                order.setDate(rs.getString("order_date"));
                order.setRequiredDate(rs.getString("required_date"));
                order.setStatus(rs.getString("status"));
                order.setSalesman(new Salesman(rs.getString("salesman_id"), ""));
                list.add(order);
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return list;
    }
    
    public List listCheques(String salesman, String boss) {
        java.sql.Connection cnn = (java.sql.Connection) InitApp.getServletContext().getAttribute("cnn");
        java.sql.Statement stmt;
        List list = new ArrayList();
        String sql;
        
        if (salesman.equals("[Todos]")) {
            sql="select cheques.check_code,cheques.check_number,cheques.amount, cheques.customer_id, customers.name, customers.salesman_id, salesmen.name as name2 " +
                    "from cheques, customers, salesmen " +
                    "where cheques.customer_id=customers.customer_id and " +
                    "customers.salesman_id = salesmen.salesman_id and " +
                    "customers.salesman_id IN (select salesmen_sub.salesman_id_sub from salesmen_sub where salesmen_sub.salesman_id='"+boss+"') " +
                    "order by cheques.check_number";
            
        } else {
            sql="select cheques.check_code,cheques.check_number,cheques.amount, cheques.customer_id, customers.name, customers.salesman_id, salesmen.name as name2 " +
                    "from cheques, customers, salesmen " +
                    "where cheques.customer_id=customers.customer_id and " +
                    "customers.salesman_id = salesmen.salesman_id and " +
                    "customers.salesman_id ='"+salesman+"' " +
                    "order by cheques.check_number";
        }
        try {
            stmt = cnn.createStatement();
            java.sql.ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Cheque ch = new Cheque();
                ch.setNumber(rs.getString("check_number"));
                ch.setCode(rs.getString("check_code"));
                ch.setAmount(rs.getDouble("amount"));
                ch.setCustomer(new Customer(rs.getString("customer_id"), rs.getString("name")));
                ch.setSalesman(new Salesman(rs.getString("salesman_id"), rs.getString("name2")));
                list.add(ch);
            }
            
            rs.close();
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return list;
    }
}
